#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Excel数据分析脚本 - 用于理解BOM和基础数据结构
"""
import openpyxl
import os
from pathlib import Path

def analyze_excel_file(file_path):
    """分析单个Excel文件"""
    print(f"\n{'='*80}")
    print(f"文件: {os.path.basename(file_path)}")
    print(f"{'='*80}")
    
    try:
        wb = openpyxl.load_workbook(file_path, data_only=True)
        print(f"\n工作表数量: {len(wb.sheetnames)}")
        print(f"工作表列表: {wb.sheetnames}")
        
        for sheet_name in wb.sheetnames:
            print(f"\n{'-'*80}")
            print(f"Sheet: {sheet_name}")
            print(f"{'-'*80}")
            
            ws = wb[sheet_name]
            
            # 获取使用的行列范围
            if ws.max_row > 0:
                print(f"数据范围: {ws.min_row}行 到 {ws.max_row}行, {ws.min_column}列 到 {ws.max_column}列")
                
                # 显示前10行数据
                print(f"\n前10行数据预览:")
                for row_idx, row in enumerate(ws.iter_rows(min_row=1, max_row=min(10, ws.max_row), values_only=True), 1):
                    # 过滤掉全空的行
                    if any(cell is not None for cell in row):
                        print(f"  行{row_idx}: {row[:15]}")  # 只显示前15列
                
                # 识别表头
                print(f"\n尝试识别表头行:")
                for row_idx in range(1, min(6, ws.max_row + 1)):
                    row = list(ws.iter_rows(min_row=row_idx, max_row=row_idx, values_only=True))[0]
                    non_empty = [cell for cell in row if cell is not None and str(cell).strip()]
                    if len(non_empty) > 3:  # 至少有3个非空单元格
                        print(f"  可能的表头(行{row_idx}): {non_empty[:20]}")
                
                # 数据统计
                print(f"\n数据统计:")
                print(f"  总行数: {ws.max_row}")
                print(f"  总列数: {ws.max_column}")
                
                # 检查层级结构(通过缩进识别)
                print(f"\n检查层级结构(前20行):")
                for row_idx in range(1, min(21, ws.max_row + 1)):
                    row = list(ws.iter_rows(min_row=row_idx, max_row=row_idx, values_only=True))[0]
                    # 检查第一列是否有缩进或空格
                    first_col = str(row[0]) if row[0] is not None else ""
                    if first_col:
                        leading_spaces = len(first_col) - len(first_col.lstrip())
                        indent_marker = "  " * (leading_spaces // 2) if leading_spaces > 0 else ""
                        print(f"  行{row_idx}: {indent_marker}[{first_col[:30]}]")
            else:
                print("  空工作表")
        
        wb.close()
        
    except Exception as e:
        print(f"错误: {e}")
        import traceback
        traceback.print_exc()

def main():
    """主函数"""
    excel_dir = Path("/root/odoo/extra-addons/cpq_qms/excel_data")
    
    print("\n" + "="*80)
    print("开始分析Excel数据文件")
    print("="*80)
    
    # 分析每个文件
    files = [
        "BMPT-P1-MS11-BOM-01-L1  MS11项目前保总成BOM清单.xlsx",
        "EL-P2-T167 -BOM-01-A0 T167 项目前保总成BOM清单_20240725.xlsx",
        "数据模版.xlsx"
    ]
    
    for filename in files:
        file_path = excel_dir / filename
        if file_path.exists():
            analyze_excel_file(file_path)
        else:
            print(f"\n文件不存在: {filename}")
    
    print("\n" + "="*80)
    print("分析完成")
    print("="*80)

if __name__ == "__main__":
    main()
